package com.community.service;

import com.community.entity.School;
import com.community.util.DateUtils;
import com.community.util.ImageUtil;
import com.community.util.Search;
import com.community.util.SpringUtil;
import com.feng.fengDUtil.BaseService;
import com.feng.fengDUtil.Page;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service
@Transactional
@Slf4j
public class SchoolService extends BaseService {

    public static final String schoolImagePath="/upload/school/";

    public Page query(Page page, Search search) {
        ArrayList params = new ArrayList();
        String sql = "select u.id,name,code,sheng,is985,is211,level,logo,videoUrl,policyUrl from a_school u  where 1=1 ";
        if(StringUtils.isNotEmpty(search.getName())){
            sql+=" and u.name like ? ";
            params.add(processLike(search.getName()));
        }
        if (StringUtils.isNotEmpty(search.getSchoolType())) {
            switch (search.getSchoolType()) {
                case "全部":
                    break;
                case "211":
                    sql += " and u.is211 = 211";
                    break;
                case "985":
                    sql += " and u.is985 = 985";
                    break;
                case "独立学院":
                    sql += " and u.school_type like '%独立学院%'";
                    break;
                case "民办本科":
                    sql += " and u.nature = '民办' and u.level='本科'";
                    break;
                case "公办本科":
                    sql += " and u.nature = '公办' and u.level='本科'";
                    break;
            }
        }
        if(StringUtils.isNotEmpty(search.getProvince())&&!"全国".equals(search.getProvince())){
            sql+=" and u.sheng = ? ";
            params.add(search.getProvince());
        }
        sql+=" order by u.ranking asc";
        findPageList(School.class,sql,page,params.toArray());
        return page;
    }

    public String existsSchool(String name) {
        return sql("select id from a_school where name = ?").one(String.class,name);
    }

    public void updateSchoolVideo(School school) {
        sql("update a_school set videoUrl = ? where id = ?").update(new Object[]{school.getVideoUrl(),school.getId()});
    }

    public void updateSchoolPolicy(School school) {
        sql("update a_school set policyUrl = ? where id = ?").update(new Object[]{school.getPolicyUrl(),school.getId()});
    }

    public Object saveImage(MultipartFile file) throws IOException {
        Map<String, String> ret = new HashMap<>();
        File basePath = new File(SpringUtil.webRoot.getAbsolutePath()+ schoolImagePath);
        if (!basePath.exists()) {
            basePath.mkdirs();
        }
        String picName =DateUtils.getUniqueName() + ImageUtil.getFileExt(file);
        String relativePath = schoolImagePath+picName;
        File f = new File(basePath.getAbsolutePath()+"/"+picName);
        file.transferTo(f);
        ret.put("url",relativePath);
        ret.put("name",file.getOriginalFilename());
        return ret;
    }

    public Workbook downloadSchools() throws IOException {
        Workbook workbook = WorkbookFactory.create(true);
        Sheet sheet = workbook.createSheet("sheet1");
        Row topRow = sheet.createRow(0);
        topRow.createCell(0).setCellValue("序号");
        topRow.createCell(1).setCellValue("校名");
        topRow.createCell(2).setCellValue("院校代码");
        topRow.createCell(3).setCellValue("省");
        topRow.createCell(4).setCellValue("985");
        topRow.createCell(5).setCellValue("211");
        topRow.createCell(6).setCellValue("级别");
        topRow.createCell(7).setCellValue("排名");
        topRow.createCell(8).setCellValue("办学性质");
        topRow.createCell(9).setCellValue("地址");

        topRow.createCell(10).setCellValue("电话");
        topRow.createCell(11).setCellValue("网址");
        topRow.createCell(12).setCellValue("邮箱");
        topRow.createCell(13).setCellValue("logo");
        topRow.createCell(14).setCellValue("招生章程url");


        String sql="select u.id,name,code,sheng,is985,is211,level,ranking,nature,address,phone,site,email,logo,policyUrl from a_school u order by ranking";
        List<School> allSchools = sql(sql).list(School.class);
        for (int i = 0; i < allSchools.size(); i++) {
            School school =  allSchools.get(i);
            Row row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(i + 1);
            row.createCell(1).setCellValue(school.getName());
            row.createCell(2).setCellValue(school.getCode());
            row.createCell(3).setCellValue(school.getSheng());
            row.createCell(4).setCellValue(school.getIs985());
            row.createCell(5).setCellValue(school.getIs211());
            row.createCell(6).setCellValue(school.getLevel());
            row.createCell(7).setCellValue(school.getRanking()+"");
            row.createCell(8).setCellValue(school.getNature());
            row.createCell(9).setCellValue(school.getAddress());

            row.createCell(10).setCellValue(school.getPhone());
            row.createCell(11).setCellValue(school.getSite());
            row.createCell(12).setCellValue(school.getEmail());
            row.createCell(13).setCellValue(school.getLogo());
            row.createCell(14).setCellValue(school.getPolicyUrl());
        }
        return workbook;
    }
}
